package org.zjx.db;

import org.zjx.common.bean.Sects;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SectsDB {
    private static boolean isTableCreated = false;  // Flag to ensure the table is only created once
    private final SQLiteHelper dbHelper;

    public SectsDB() {
        dbHelper = SQLiteHelper.getSingleton();
        // Only create the table once when the instance is first created
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // Create table for Sects
    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS sects (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    division TEXT NOT NULL,\n"
                + "    f8ScreenId INTEGER NOT NULL,\n"
                + "    f8Location TEXT NOT NULL,\n"
                + "    masterId INTEGER NOT NULL,\n"
                + "    brotherScreenId INTEGER NOT NULL,\n"
                + "    brotherLocation TEXT NOT NULL\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    // Insert a Sects record
    public void insert(Sects sects) {
        String sql = "INSERT INTO sects(division, f8ScreenId, f8Location, masterId, brotherScreenId, brotherLocation) VALUES(?, ?, ?, ?, ?, ?)";
        Object[] params = {
                sects.getDivision(),
                sects.getF8ScreenId(),
                sects.getF8Location(),
                sects.getMasterId(),
                sects.getBrotherScreenId(),
                sects.getBrotherLocation()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // Update a Sects record
    public void update(Sects sects) {
        String sql = "UPDATE sects SET division = ?, f8ScreenId = ?, f8Location = ?, masterId = ?, brotherScreenId = ?, brotherLocation = ? WHERE id = ?";
        Object[] params = {
                sects.getDivision(),
                sects.getF8ScreenId(),
                sects.getF8Location(),
                sects.getMasterId(),
                sects.getBrotherScreenId(),
                sects.getBrotherLocation(),
                sects.getId()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // Delete a Sects record by ID
    public void deleteById(int id) {
        String sql = "DELETE FROM sects WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    // Find Sects by ID
    public Sects findById(int id) {
        String sql = "SELECT * FROM sects WHERE id = ?";
        Object[] params = {id};
        List<Sects> sectsList = dbHelper.executeQuery(sql, params, Sects.class);
        if (sectsList.isEmpty()) return null;
        return sectsList.get(0);
    }

    // Find Sects by Division Name
    public Sects findByDivision(String division) {
        String sql = "SELECT * FROM sects WHERE division = ?";
        Object[] params = {division};
        List<Sects> sectsList = dbHelper.executeQuery(sql, params, Sects.class);
        if (sectsList.isEmpty()) return null;
        return sectsList.get(0);
    }

    // Get all Sects
    public List<Sects> findAll() {
        String sql = "SELECT * FROM sects";
        Object[] params = {};
        return dbHelper.executeQuery(sql, params, Sects.class);
    }

    // Paginated query for Sects
    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;

        String sql = "SELECT * FROM sects LIMIT ? OFFSET ?";
        Object[] params = {pageSize, offset};
        List<Sects> sectsList = dbHelper.executeQuery(sql, params, Sects.class);

        String countSql = "SELECT COUNT(*) FROM sects";
        Object[] countParams = {};
        List<Integer> totalRecordsList = dbHelper.executeQuery(countSql, countParams, Integer.class);
        int totalRecords = totalRecordsList.isEmpty() ? 0 : totalRecordsList.get(0);

        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("sectsList", sectsList);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    // Clear all Sects data
    public void clearData() {
        dbHelper.clearData("sects");
    }

    // Drop the Sects table
    public void dropTable() {
        dbHelper.dropTable("sects");
    }
}
